<!DOCTYPE html>
<html lang=zh>
<head>
    <meta charset="utf-8">
    
    <title>MySQL主从复制 | YLFJM的博客</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
    <meta name="description" content="MySQL主从复制(Master-Slave)实践MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。  主从复制：简单的说也就是数据库服务器间的数据复制。">
<meta name="keywords" content="原创,MySQL">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL主从复制">
<meta property="og:url" content="https://ylfjmy.gitee.io/2018/07/28/mysql-master-slave-copy/index.html">
<meta property="og:site_name" content="YLFJM的博客">
<meta property="og:description" content="MySQL主从复制(Master-Slave)实践MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。  主从复制：简单的说也就是数据库服务器间的数据复制。">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="https://ylfjmy.gitee.io/2018/07/28/mysql-master-slave-copy/1532759363215.png">
<meta property="og:image" content="https://ylfjmy.gitee.io/2018/07/28/mysql-master-slave-copy/1043616-20161213151808011-1732852037.jpg">
<meta property="og:image" content="https://ylfjmy.gitee.io/2018/07/28/mysql-master-slave-copy/1532761887616.png">
<meta property="og:image" content="https://ylfjmy.gitee.io/2018/07/28/mysql-master-slave-copy/1532762915171.png">
<meta property="og:updated_time" content="2018-07-28T08:18:12.909Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL主从复制">
<meta name="twitter:description" content="MySQL主从复制(Master-Slave)实践MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。  主从复制：简单的说也就是数据库服务器间的数据复制。">
<meta name="twitter:image" content="https://ylfjmy.gitee.io/2018/07/28/mysql-master-slave-copy/1532759363215.png">
    

    

    
        <link rel="icon" href="/favicon.ico" />
    

    <link rel="stylesheet" href="/vendor/font-awesome/css/font-awesome.min.css">
    <link rel="stylesheet" href="/vendor/open-sans/styles.css">
    <link rel="stylesheet" href="/vendor/source-code-pro/styles.css">

    <link rel="stylesheet" href="/css/style.css">

    <script src="/vendor/jquery/2.1.3/jquery.min.js"></script>
    
    
        <link rel="stylesheet" href="/vendor/lightgallery/css/lightgallery.min.css">
    
    
    
    
    



</head>

<body>
    <div id="container">
        <header id="header">
    <div id="header-main" class="header-inner">
        <div class="outer">
            <a href="/" id="logo">
                <i class="logo"></i>
                <span class="site-title">YLFJM的博客</span>
            </a>
            <nav id="main-nav">
                
                    <a class="main-nav-link" href="/.">YLFJM</a>
                
                    <a class="main-nav-link" href="/archives">归档</a>
                
                    <a class="main-nav-link" href="/categories">分类</a>
                
                    <a class="main-nav-link" href="/about">关于我</a>
                
            </nav>
            
                
                <nav id="sub-nav">
                    <div class="profile" id="profile-nav">
                        <a id="profile-anchor" href="javascript:;">
                            <img class="avatar" src="/css/images/logo.jpg" />
                            <i class="fa fa-caret-down"></i>
                        </a>
                    </div>
                </nav>
            
            <!--
<div id="search-form-wrap">

    <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="搜索"><button type="submit" class="search-form-submit"> </button><input type="hidden" name="sitesearch" value="https://ylfjmy.gitee.io"></form>

</div>
-->
        </div>
    </div>
    <div id="main-nav-mobile" class="header-sub header-inner">
        <table class="menu outer">
            <tr>
                
                    <td><a class="main-nav-link" href="/.">YLFJM</a></td>
                
                    <td><a class="main-nav-link" href="/archives">归档</a></td>
                
                    <td><a class="main-nav-link" href="/categories">分类</a></td>
                
                    <td><a class="main-nav-link" href="/about">关于我</a></td>
                
                <td>
                    
    <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="搜索"><input type="hidden" name="sitesearch" value="https://ylfjmy.gitee.io"></form>

                </td>
            </tr>
        </table>
    </div>
</header>

        <div class="outer">
            
                

<aside id="profile">
    <div class="inner profile-inner">
        <div class="base-info profile-block">
            <img id="avatar" src="/css/images/logo.jpg" />
            <h2 id="name">YLFJM</h2>
            <h3 id="title">Java程序猿</h3>
            <span id="location"><i class="fa fa-map-marker"></i>Shanghai, China</span>
            <!--<a id="follow" target="_blank" href="">关注我</a>-->
        </div>
        <div class="article-info profile-block">
            <div class="article-info-block">
                10
                <span>文章</span>
            </div>
            <div class="article-info-block">
                9
                <span>标签</span>
            </div>
        </div>
        
        <div class="profile-block social-links">
            <table>
                <tr>
                    
                    
                    <td>
                        <a href="http://github.com/ylfjm/" target="_blank" title="GITHUB" class=tooltip>
                            <i class="fa fa-github"></i>
                        </a>
                    </td>
                    
                    <td>
                        <a href="https://gitee.com/ylfjmy/" target="_blank" title="码云" class=tooltip>
                            <i class="fa fa-gg"></i>
                        </a>
                    </td>
                    
                    <td>
                        <a href="mailto:bozwork@163.com" target="_blank" title="邮件" class=tooltip>
                            <i class="fa fa-envelope"></i>
                        </a>
                    </td>
                    
                </tr>
            </table>
        </div>
        
    </div>
</aside>

            
            <section id="main"><article id="post-mysql-master-slave-copy" class="article article-type-post" itemscope itemprop="blogPost">
    <div class="article-inner">
        
        
            <header class="article-header">
                
    
        <h1 class="article-title" itemprop="name">
            MySQL主从复制
        </h1>
    

                
                    <div class="article-meta">
                        
    
        
        <span class="label label-success"
              style="float: left;padding: 3px 5px 3px 5px;margin-right: 15px;
                      margin-top: 3px; font-size: 0.9em;">原创</span>
        
    
        
    

                        
    <div class="article-date">
        <i class="fa fa-calendar"></i>
        <a href="/2018/07/28/mysql-master-slave-copy/">
            <time datetime="2018-07-28T07:34:16.000Z" itemprop="datePublished">2018-07-28</time>
        </a>
    </div>


                        
    <div class="article-category">
    	<i class="fa fa-folder"></i>
        <a class="article-category-link" href="/categories/MySQL/">MySQL</a>
    </div>

                        <!-- 
    <div class="article-tag">
        <i class="fa fa-tag"></i>
        <a class="tag-link" href="/tags/MySQL/">MySQL</a>, <a class="tag-link" href="/tags/原创/">原创</a>
    </div>
 -->
                    </div>
                
            </header>
        
        
        <div class="article-entry" itemprop="articleBody">
        
            
            <h4 id="MySQL主从复制-Master-Slave-实践"><a href="#MySQL主从复制-Master-Slave-实践" class="headerlink" title="MySQL主从复制(Master-Slave)实践"></a>MySQL主从复制(Master-Slave)实践</h4><p>MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。 </p>
<p>主从复制：简单的说也就是数据库服务器间的数据复制。</p>
<a id="more"></a>
<p>下面这张图简单描述了多个数据库之间主从复制与读写分离的模型：</p>
<p><img src="/2018/07/28/mysql-master-slave-copy/1532759363215.png" alt="1532759363215"></p>
<p>在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术在这里不作详述，本文主要讲实现一主一从的主从复制功能。</p>
<p>Mysql主从复制的实现原理图大致如下（来源网络）：</p>
<p><img src="/2018/07/28/mysql-master-slave-copy/1043616-20161213151808011-1732852037.jpg" alt="img"> </p>
<p>MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。 </p>
<h4 id="主从复制需要进行的配置"><a href="#主从复制需要进行的配置" class="headerlink" title="主从复制需要进行的配置"></a><strong>主从复制需要进行的配置</strong></h4><ul>
<li><p>准备工作</p>
<p>准备两台已经安装好的mysql服务器mysql-master（192.168.0.1）和mysql-slave（192.168.0.2）</p>
</li>
<li><p>主服务器配置</p>
<ol>
<li><p>配置唯一的server-id、开启二进制日志</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">[mysqld]</span><br><span class="line">log-bin=mysql-bin</span><br><span class="line">server-id=1</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建一个slave服务器可以登录的用于和master服务器通信的MySQL数据库账号，用户名：slave01，密码：slave01，并给slave01赋上相应的权限</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;GRANT REPLICATION SLAVE ON *.* TO &apos;slave01&apos;@&apos;192.168.0.2&apos; IDENTIFIED BY &apos;slave01&apos;;</span><br><span class="line">mysql&gt;FLUSH PRIVILEGES;</span><br></pre></td></tr></table></figure>
</li>
<li><p>获得master服务器二进制日志文件名及位置，并记录下File和Position这两列的值，配置slave服务器的时候会用到</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;SHOW MASTER STATUS;</span><br></pre></td></tr></table></figure>
<p><img src="/2018/07/28/mysql-master-slave-copy/1532761887616.png" alt="1532761887616"></p>
</li>
</ol>
</li>
<li><p>从服务器配置</p>
<ol>
<li><p>配置唯一的server-id、开启二进制日志</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">[mysqld]</span><br><span class="line">log-bin=mysql-bin</span><br><span class="line">server-id=2</span><br></pre></td></tr></table></figure>
</li>
<li><p>告知slave服务器master服务器的二进制文件名与位置</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;CHANGE MASTER TO MASTER_HOST=&apos;192.168.0.1&apos;,MASTER_USER=&apos;slave01&apos;,MASTER_PASSWORD=&apos;slave01&apos;,MASTER_LOG_FILE=&apos;mysql-bin.000008&apos;,MASTER_LOG_POS=197794147;</span><br></pre></td></tr></table></figure>
</li>
<li><p>启用slave服务</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt;START SLAVE;</span><br></pre></td></tr></table></figure>
</li>
</ol>
</li>
</ul>
<p>至此，mysql的主从复制配置就算完成了。下面我们在slave服务器上执行SHOW SLAVE STATUS\G命令查看我们的配置是否成功</p>
<p><img src="/2018/07/28/mysql-master-slave-copy/1532762915171.png" alt="1532762915171"></p>
<p>只有当Slave_IO_Running和Slave_SQL_Running这两项的值都是Yes的时候，才表明状态是正常的。</p>
<h4 id="常见问题"><a href="#常见问题" class="headerlink" title="常见问题"></a>常见问题</h4><p>当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示，可根据错误提示进行更正。 </p>
<p><strong>常见出错点：</strong></p>
<ol>
<li>两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。</li>
<li>已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。</li>
<li>stop slave后，数据变更，再start slave。出错。</li>
</ol>

        
        </div>
        <footer class="article-footer">
            
    <div class="article-tag">
        <i class="fa fa-tag"></i>
        <a class="tag-link" href="/tags/MySQL/">MySQL</a>, <a class="tag-link" href="/tags/原创/">原创</a>
    </div>

            <div class="share-container">



</div>

    <!--
<a data-url="https://ylfjmy.gitee.io/2018/07/28/mysql-master-slave-copy/" data-id="cktmb8pti000hf0p6trpm4y67" class="article-share-link"><i class="fa fa-share"></i>分享到</a>
<script>
    (function ($) {
        // Prevent duplicate binding
        if (typeof(__SHARE_BUTTON_BINDED__) === 'undefined' || !__SHARE_BUTTON_BINDED__) {
            __SHARE_BUTTON_BINDED__ = true;
        } else {
            return;
        }
        $('body').on('click', function() {
            $('.article-share-box.on').removeClass('on');
        }).on('click', '.article-share-link', function(e) {
            e.stopPropagation();

            var $this = $(this),
                url = $this.attr('data-url'),
                encodedUrl = encodeURIComponent(url),
                id = 'article-share-box-' + $this.attr('data-id'),
                offset = $this.offset(),
                box;

            if ($('#' + id).length) {
                box = $('#' + id);

                if (box.hasClass('on')){
                    box.removeClass('on');
                    return;
                }
            } else {
                var html = [
                    '<div id="' + id + '" class="article-share-box">',
                        '<input class="article-share-input" value="' + url + '">',
                        '<div class="article-share-links">',
                            '<a href="https://twitter.com/intent/tweet?url=' + encodedUrl + '" class="fa fa-twitter article-share-twitter" target="_blank" title="Twitter"></a>',
                            '<a href="https://www.facebook.com/sharer.php?u=' + encodedUrl + '" class="fa fa-facebook article-share-facebook" target="_blank" title="Facebook"></a>',
                            '<a href="http://pinterest.com/pin/create/button/?url=' + encodedUrl + '" class="fa fa-pinterest article-share-pinterest" target="_blank" title="Pinterest"></a>',
                            '<a href="https://plus.google.com/share?url=' + encodedUrl + '" class="fa fa-google article-share-google" target="_blank" title="Google+"></a>',
                        '</div>',
                    '</div>'
                ].join('');

              box = $(html);

              $('body').append(box);
            }

            $('.article-share-box.on').hide();

            box.css({
                top: offset.top + 25,
                left: offset.left
            }).addClass('on');

        }).on('click', '.article-share-box', function (e) {
            e.stopPropagation();
        }).on('click', '.article-share-box-input', function () {
            $(this).select();
        }).on('click', '.article-share-box-link', function (e) {
            e.preventDefault();
            e.stopPropagation();

            window.open(this.href, 'article-share-box-window-' + Date.now(), 'width=500,height=450');
        });
    })(jQuery);
</script>
-->

            
    

        </footer>
    </div>
    
        
<nav id="article-nav">
    
        <a href="/2018/07/29/CentOS下安装MySQL5.7.20/" id="article-nav-newer" class="article-nav-link-wrap">
            <strong class="article-nav-caption">上一篇</strong>
            <div class="article-nav-title">
                
                    CentOS下安装MySQL5.7.20
                
            </div>
        </a>
    
    
</nav>


    
</article>


    <!--

    <section id="comments">
    
    </section>

-->
</section>
            
                <aside id="sidebar">
   
        
    <div class="widget-wrap">
        <h3 class="widget-title">分类</h3>
        <div class="widget">
            <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/Docker/">Docker</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Java/">Java</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Linux/">Linux</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/MySQL/">MySQL</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/Redis/">Redis</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/git/">git</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/其它/">其它</a><span class="category-list-count">1</span></li></ul>
        </div>
    </div>

    
        
    <div class="widget-wrap">
        <h3 class="widget-title">归档</h3>
        <div class="widget">
            <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/09/">九月 2021</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/12/">十二月 2018</a><span class="archive-list-count">5</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/09/">九月 2018</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/08/">八月 2018</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/07/">七月 2018</a><span class="archive-list-count">2</span></li></ul>
        </div>
    </div>

    
        
    <div class="widget-wrap">
        <h3 class="widget-title">标签</h3>
        <div class="widget">
            <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/Docker/">Docker</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Java/">Java</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Linux/">Linux</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/MySQL/">MySQL</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Redis/">Redis</a><span class="tag-list-count">2</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/git/">git</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/其它/">其它</a><span class="tag-list-count">1</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/原创/">原创</a><span class="tag-list-count">7</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/转载/">转载</a><span class="tag-list-count">3</span></li></ul>
        </div>
    </div>

    
        
    <div class="widget-wrap">
        <h3 class="widget-title">标签云</h3>
        <div class="widget tagcloud">
            <a href="/tags/Docker/" style="font-size: 10px;">Docker</a> <a href="/tags/Java/" style="font-size: 10px;">Java</a> <a href="/tags/Linux/" style="font-size: 13.33px;">Linux</a> <a href="/tags/MySQL/" style="font-size: 13.33px;">MySQL</a> <a href="/tags/Redis/" style="font-size: 13.33px;">Redis</a> <a href="/tags/git/" style="font-size: 10px;">git</a> <a href="/tags/其它/" style="font-size: 10px;">其它</a> <a href="/tags/原创/" style="font-size: 20px;">原创</a> <a href="/tags/转载/" style="font-size: 16.67px;">转载</a>
        </div>
    </div>

    
        
    <div class="widget-wrap widget-list">
        <h3 class="widget-title">链接</h3>
        <div class="widget">
            <ul>
                
                    <li>
                        <a href="http://hexo.io">Hexo</a>
                    </li>
                
            </ul>
        </div>
    </div>


    
    <div id="toTop" class="fa fa-angle-up"></div>
</aside>
            
        </div>
        <!--<footer id="footer">
    <div class="outer">
        <div id="footer-info" class="inner">
            &copy; 2021 Zhang Bo<br>
            Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>. Theme by <a href="http://github.com/ppoffice">PPOffice</a>
        </div>
    </div>
</footer>-->
        

    
        <script src="/vendor/lightgallery/js/lightgallery.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-thumbnail.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-pager.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-autoplay.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-fullscreen.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-zoom.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-hash.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-share.min.js"></script>
        <script src="/vendor/lightgallery/js/lg-video.min.js"></script>
    
    



<!-- Custom Scripts -->
<script src="/js/main.js"></script>

    </div>
</body>
</html>